import pandas as pd
from sqlalchemy import func, or_, and_, exc, insert, literal

from app.config.db import DBUtils
from app.model.FKListModel import fklist
from app.model.SFKTempListModel import sfkTempList
from app.model.SKListModel import sklist

db_utils = DBUtils()

class SFKListTempSyncService:

    def __init__(self):
        self.db = DBUtils()

    def dataSync(self, session):
        # 付款流水导入临时表
        fk_stmt = insert(sfkTempList).from_select(
            ['FKZHMC', 'FKGS', 'SKZHMC', 'SKGS', 'FKJE', 'JS_YWFL', 'RQ', 'SCRQ', 'JYND', 'IS_SFK'],
            session.query(fklist.FKZHMC, fklist.FKGS, fklist.SKZHMC, fklist.SKGS, fklist.FKJE, fklist.JS_YWFL, fklist.RQ, fklist.SCRQ, fklist.JYND, literal(1).label('IS_SFK'))
        )
        session.execute(fk_stmt)
        # 收款流水导入临时表
        sk_stmt = insert(sfkTempList).from_select(
            ['FKZHMC', 'FKGS', 'SKZHMC', 'SKGS', 'FKJE', 'JS_YWFL', 'RQ', 'SCRQ', 'JYND', 'IS_SFK'],
            session.query(sklist.FKZHMC, sklist.FKGS, sklist.SKZHMC, sklist.SKGS, sklist.FKJE, sklist.JS_YWFL,
                          sklist.RQ, sklist.SCRQ, sklist.JYND, literal(0).label('IS_SFK'))
        )
        session.execute(sk_stmt)
        session.commit()

    def pandasSync(self,engine):
        fk_df = pd.read_sql("SELECT FKZHMC, FKGS, SKZHMC, SKGS, FKJE, JS_YWFL, RQ, SCRQ, JYND FROM HX_BI_FKLIST", engine)
        sk_df = pd.read_sql("SELECT FKZHMC, FKGS, SKZHMC, SKGS, FKJE, JS_YWFL, RQ, SCRQ, JYND FROM HX_BI_SKLIST", engine)
        # 给 fklist 添加 IS_SFK=1
        fk_df["IS_SFK"] = 1
        # 给 sklist 添加 IS_SFK=0
        sk_df["IS_SFK"] = 0
        # 合并两张表，数据全部在内存中
        merged_df = pd.concat([fk_df, sk_df], ignore_index=True)

session = db_utils.get_session()
sfkListTempSyncService = SFKListTempSyncService()
sfkListTempSyncService.dataSync(session)